/**
 * Mikael Nilsson
 * Johan Schedin Jigland
 * Nikolai Padyukov
 * Arkitektur och design av globala applikationer, IV1201
 * 
 * 2011-03-02
 *
 * This software is provided 'as-is', without any express or implied
 * warranty.  In no event will the authors be held liable for any damages
 * arising from the use of this software.
 *
 * Permission is granted to anyone to use this software for any purpose,
 * including commercial applications, and to alter it and redistribute it
 * freely, subject to the following restrictions:
 *
 * 1. The origin of this software must not be misrepresented; you must not
 *    claim that you wrote the original software. If you use this software
 *    in a product, an acknowledgment in the product documentation would be
 *    appreciated but is not required.
 * 2. Altered source versions must be plainly marked as such, and must not be
 *    misrepresented as being the original software.
 * 3. This notice may not be removed or altered from any source distribution.
 */

package org.kth.dbcopy.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;

import org.kth.dbcopy.observer.TransferObserver;

import com.mysql.jdbc.Driver;

/**
 * This class implements Runnable and when its run, it transfer the old database to the new one.
 *  
 *  The old database was specified in the project task of the KTH course IV1201.
 *  The new database was created by us, group 2, for the project task of the KTH course IV1201.
 * 
 * @author Mikael Nilsson
 *
 */
public class DBCopyer implements Runnable {
	private Connection old_con;
	private Connection new_con;
	private boolean success = true;
	
	private HashMap<Integer, Person> persons = new HashMap<Integer, Person>();
	private HashMap<Integer, Role> roles = new HashMap<Integer, Role>();
	private HashMap<Integer, CompetenceProfile> competenceProfiles = new HashMap<Integer, CompetenceProfile>();
	private HashMap<Integer, Competence> competences = new HashMap<Integer, Competence>();
	private HashMap<Integer, Availability> availabilities = new HashMap<Integer, Availability>();
	
	private TransferObserver observer;
	
	/**
	 * The only constructor for DBCopyer.
	 * 
	 * @param old_userName The username for the old database connection.
	 * @param old_password The password the the username on the old database connection.
	 * @param old_url The jdbc url for the old database.
	 * @param new_userName The username for the new database connection.
	 * @param new_password The password the the username on the new database connection.
	 * @param new_url The jdbc url for the new database.
	 * @param observer The <class>TransferObserver</class> to observe the transfer.
	 */
	public DBCopyer(String old_userName, String old_password, String old_url,
			String new_userName, String new_password, String new_url, TransferObserver observer) {
		this.observer = observer;
		try {
			DriverManager.registerDriver(new Driver());
			
			old_con = DriverManager.getConnection(old_url, old_userName, old_password);
			old_con.setAutoCommit(false);
			new_con = DriverManager.getConnection(new_url, new_userName, new_password);
			new_con.setAutoCommit(false);
		} catch(SQLException e) {
			e.printStackTrace();
			observer.exception(e.getMessage());
			success = false;
		}
	}

	/**
	 * Runs the transfer if the connection was successfull.
	 */
	public void run() {
		if (success) {
			try {
				Statement stmt = old_con.createStatement();
				ResultSet rs = stmt.executeQuery("SELECT * FROM role");
				
				//roles
				while (rs.next()) {
					Role role = new Role();
					role.setName(rs.getString("name"));
					
					roles.put(rs.getInt("role_id"), role);
				}
				
				stmt = old_con.createStatement();
				rs = stmt.executeQuery("SELECT * FROM competence");
				
				//competences
				while (rs.next()) {
					Competence competence = new Competence();
					competence.setName(removeWhiteSpaces(rs.getString("name")));
					
					competences.put(rs.getInt("competence_id"), competence);
				}
				
				stmt = old_con.createStatement();
				rs = stmt.executeQuery("SELECT * FROM person");
				
				//persons
				while (rs.next()) {
					Person person = new Person();
					person.setName(rs.getString("name"));
					person.setSurname(rs.getString("surname"));
					person.setSsn(rs.getString("ssn"));
					person.setEmail(rs.getString("email"));
					person.setPassword(rs.getString("password"));
					person.setUsername(rs.getString("username"));
					person.setRole(roles.get(rs.getInt("role_id")));
					
					persons.put(rs.getInt("person_id"), person);
				}
				
				stmt = old_con.createStatement();
				rs = stmt.executeQuery("SELECT * FROM availability");
				
				//availability
				while (rs.next()) {
					Availability availability = new Availability();
					availability.setPerson(persons.get(rs.getInt("person_id")));
					availability.setFromDate(rs.getDate("from_date"));
					availability.setToDate(rs.getDate("to_date"));
					
					availabilities.put(rs.getInt("availability_id"), availability);
				}
				
				stmt = old_con.createStatement();
				rs = stmt.executeQuery("SELECT * FROM competence_profile");
				
				//competence profiles
				while (rs.next()) {
					CompetenceProfile cp = new CompetenceProfile();
					cp.setCompetence(competences.get(rs.getInt("competence_id")));
					cp.setPerson(persons.get(rs.getInt("person_id")));
					cp.setYearsOfExperience(rs.getInt("years_of_experience"));
					
					competenceProfiles.put(rs.getInt("competence_profile_id"), cp);
				}				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			try {
				Statement stmt = new_con.createStatement();
				
				for (int key : competences.keySet()) {
					Competence competence = competences.get(key);

					stmt.executeUpdate("INSERT INTO competencecategory(name, description) " +
							"VALUES (\"" + competence.getName() + "\",\"\")");
				}
				
				for (int key : persons.keySet()) {
					Person person = persons.get(key);

					
					stmt.executeUpdate("INSERT INTO person(firstName,lastName,emailAdress) " +
							"VALUES (\"" + person.getName() + "\"," +
							"\"" + person.getSurname() + "\"," +
							"\"" + person.getEmail() + "\")", Statement.RETURN_GENERATED_KEYS);
					
					int p_id = 0;
					ResultSet rs = stmt.getGeneratedKeys();
					if (rs.next()) {
						p_id = rs.getInt(1);
					} else {
						System.err.println("Couldn't retrieve generated key for person " + person.getName() + ".");
						continue;
					}
					
					String pw;
					if (person.getPassword() == null) {
						pw = (long)(Math.random()*Long.MAX_VALUE) + "" + (long)(Math.random()*Long.MAX_VALUE) + "" + (long)(Math.random()*Long.MAX_VALUE);
					} else {
						pw = person.getPassword();
					}
					
					String userName;
					if (person.getUsername() == null) {
						userName = person.getName() + person.getSurname() + (long)(Math.random()*Long.MAX_VALUE);
					} else {
						userName = person.getUsername();
					}

					stmt.execute("INSERT INTO account(userName,userPassword,person) " +
							  "VALUES (\"" + userName + "\"," + "\"" + pw + "\"," + p_id + ")");
					
					if (person.getRole().getName().equals("job_seeker")) {
						stmt.executeUpdate("INSERT INTO jobseeker(account) VALUES(\"" + userName + "\")", Statement.RETURN_GENERATED_KEYS);
					
						rs = stmt.getGeneratedKeys();
						int js_id = 0;
						if (rs.next()) {
							js_id = rs.getInt(1);
						} else {
							System.err.println("Couldn't retrieve generated key for jobseeker account of person " + person.getName() + ".");
							continue;
						}
						
						
						
						List<Availability> availabilities =  getPersonAvailabilities(person);
						
						for (Availability av : availabilities) {
							PreparedStatement ps = new_con.prepareStatement("INSERT INTO timeperiod(startDate, endDate, jobSeeker) " +
									"VALUES(?,?,?)");
							
							ps.setDate(1, new java.sql.Date(av.getFromDate().getTime()));
							ps.setDate(2, new java.sql.Date(av.getToDate().getTime()));
							ps.setInt(3, js_id);
							
							ps.executeUpdate();
						}
						
						for (CompetenceProfile cp : getPersonCompetenceProfiles(person)) {
							PreparedStatement ps = new_con.prepareStatement("INSERT INTO competence(category, comment, numberOfMonths,jobSeeker) " +
															"VALUES(?,?,?,?)");
							ps.setString(1, cp.getCompetence().getName());
							ps.setString(2, "");
							ps.setInt(3, cp.getYearsOfExperience()*12);
							ps.setInt(4, js_id);
							
							ps.executeUpdate();
						}
						
						PreparedStatement ps = 
							new_con.prepareStatement("INSERT INTO jobapplication(jobSeeker,dateOfRegistration,accepted) " +
														"VALUES(?,?,?)");
						ps.setInt(1, js_id);
						ps.setDate(2, new java.sql.Date(new Date().getTime()));
						ps.setBoolean(3, false);
						
						ps.executeUpdate();
						
					} else if (person.getRole().getName().equals("recruit")) {	
						stmt.executeUpdate("INSERT INTO recruiter(account) VALUES(\"" + userName + "\")");
					} else {
						System.err.println(person.getName() + " " + person.getSurname() + " fell off....");
					}
				}
				
				new_con.commit();
				observer.done();
			} catch (Exception e) {
				e.printStackTrace();
				try {
					new_con.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
				observer.exception(e.getMessage());
			}
		}
	}

	private String removeWhiteSpaces(String string) {
		StringBuilder sb = new StringBuilder(string);
		for (int i = 0; i < string.length(); i++) {
			if (sb.charAt(i) == ' ') {
				sb.deleteCharAt(i);
				i--;
			}
		}
		return sb.toString();
	}

	private List<Availability> getPersonAvailabilities(Person person) {
		List<Availability> list = new LinkedList<Availability>();
		
		for (int key : availabilities.keySet()) {
			if (availabilities.get(key).getPerson().equals(person)) {
				list.add(availabilities.get(key));
			}
		}
		
		return list;
	}
	
	private List<CompetenceProfile> getPersonCompetenceProfiles(Person person) {
		List<CompetenceProfile> list = new LinkedList<CompetenceProfile>();
		
		for (int key : competenceProfiles.keySet()) {
			if (competenceProfiles.get(key).getPerson().equals(person)) {
				list.add(competenceProfiles.get(key));
			}			
		}
		
		return list;
	}
}
